﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DAL;

namespace BLL
{
    public class COMMENTBLL
    {
        DataService DB = new DataService();

        public void insertComment(string noidung, DateTime ngaygui, int mathanhvien, int mabaiviet) {
            string sql = "insert into comment values(N'" + noidung + "','" + mabaiviet.ToString() + "','"
                + ngaygui.ToString() + "','" + mathanhvien.ToString() + "')";
            DB.ExecuteNonQuery(sql);
        }

        public void update(int macomment, string noidung, int mabaiviet, DateTime ngaygui, string file_patch)
        {
            string sql = "update comment set noidung = N'" + noidung + ", mabaiviet = '" + mabaiviet.ToString() + "', ngaygui = '" + ngaygui.ToString() + "', file_patch = '" + file_patch + "' where macomment = " + macomment.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public void delete(int macomment)
        {
            string sql = "delete from comment where macomment = " + macomment.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public int getCountOfCommentByIDBaiviet(int idBaiviet) {
            string sql = "select count(*) from baiviet bv,comment cme "
                        + " where cme.mabaiviet = bv.mabaiviet and cme.mabaiviet = " + idBaiviet.ToString();

            return Int32.Parse(DB.ExecuteScalar(sql));

        }

        public int count() {
            string sql = "select count(*) from comment";
            return Int32.Parse(DB.ExecuteScalar(sql));
        }

        public int getCountOfCommentByIDPhumuc(int idPhumuc) {
            string sql = "select count(*) from phumuc pm, baiviet bv, comment cme "
                        + " where pm.maphumuc = bv.maphumuc and bv.mabaiviet = cme.mabaiviet "
                        + " and pm.maphumuc = " + idPhumuc.ToString();
            return Int32.Parse(DB.ExecuteScalar(sql));
        }

        public List<COMMENT> getCommentWithIDBaiviet(int idBaiviet) {
            string sql = "select cme.macomment, cme.noidung, cme.mabaiviet, cme.ngaygui, cme.mathanhvien from thanhvien tv,comment cme,baiviet bv "
                        + "where tv.mathanhvien = cme.mathanhvien and cme.mabaiviet = bv.mabaiviet and bv.mabaiviet = " + idBaiviet.ToString() + " order by ngaygui DESC";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<COMMENT> lstCm = new List<COMMENT>();
            foreach (DataRow r in table.Rows)
            {
                COMMENT cme = new COMMENT();
                cme.macomment = (int)r[0];
                cme.noidung = (string)r[1];
                cme.mabaiviet = (int)r[2];
                cme.ngaygui = Convert.ToDateTime(r[3]);
                cme.mathanhvien = (int)r[4];

                lstCm.Add(cme);
            }
            return lstCm;
        }
    }
}
